package elka.sag.logisticsystem.service.impl;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

import elka.sag.logisticsystem.bean.*;
import elka.sag.logisticsystem.bean.Order.OrderStatus;
import elka.sag.logisticsystem.service.RepositoryService;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

public class JdbcRepositoryService implements RepositoryService {
//	private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;

	public JdbcRepositoryService(DataSource dataSource) {
//		this.dataSource = dataSource;
        this.jdbcTemplate = new JdbcTemplate(dataSource);
	}

    public List<Order> getOrderForProcessing() {
        List<Order> orders = this.jdbcTemplate.query(
            "UPDATE logisticsystem.zamowienia SET " +
            "czy_przetwarzane = true " +
//                        "  zamowienia.id_zamowienia, " +
//                        "  zamowienia.data_zlozenia, " +
//                        "  zamowienia.kod_ostatniej_akcji, " +
//                        "  zamowienia.komunikat_ostatniej_akcji, " +
//                        "  zamowienia.czy_przetwarzane, " +
//                        "  zamowienia.id_adresu, " +
//                        "  zamowienia.status " +
            "WHERE " +
            "czy_przetwarzane = false " +
            "and status <> 'COMPLETE'" +
            "RETURNING * ;",

            new RowMapper<Order>() {
                public Order mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Order order = new Order();
                    order.setId(rs.getLong("id_zamowienia"));
                    order.setStatus(Order.OrderStatus.valueOf(rs.getString("status")));
                    order.setDate(rs.getTimestamp("data_zlozenia"));
                    order.setLastActionCode(rs.getInt("kod_ostatniej_akcji"));
                    order.setLastActionComment(rs.getString("komunikat_ostatniej_akcji"));
                    order.setProcess(rs.getBoolean("czy_przetwarzane"));
                    return order;
                }
            });
        return orders;
	}

    /** Helper function for testing */
    public void updateOrdersSetProcessedFalse() {
        this.jdbcTemplate.update(
            "UPDATE logisticsystem.zamowienia SET czy_przetwarzane = false "
        );
    }

	public List<BusinesProcesStatus> getBusinesProcesStatuses() {
        List<BusinesProcesStatus> businesProcesStatuses = this.jdbcTemplate.query(
            "SELECT" +
                "  statusy.nazwa as status, " +
                "  aktorzy.nazwa as aktor, " +
                "  statusy.liczba_aktorow " +
            "FROM" +
                "  logisticsystem.statusy, " +
                "  logisticsystem.aktorzy " +
            "WHERE" +
                "  statusy.id_aktora = aktorzy.id_aktora;",

            new RowMapper<BusinesProcesStatus>() {
                public BusinesProcesStatus mapRow(ResultSet rs, int rowNum) throws SQLException {
                    BusinesProcesStatus businesProcesStatus = new BusinesProcesStatus();
                    businesProcesStatus.setStatus(Order.OrderStatus.valueOf(rs.getString("status")));
                    businesProcesStatus.setActorName(rs.getString("aktor"));
                    businesProcesStatus.setNumberOfActors(rs.getInt("liczba_aktorow"));
                    return businesProcesStatus;
                }
            });
		return businesProcesStatuses;
	}

//	public void updateOrder(Order order) {
//        this.jdbcTemplate.update(
//                "UPDATE logisticsystem.zamowienia " +
//                "SET " +
////                    "kod_ostatniej_akcji = ?, " +
////                    "komunikat_ostatniej_akcji = ?, " +
////                    "czy_przetwarzane = ?, " +
////                    "id_adresu = ?, " +
//                    "status = ? " +
//                "WHERE id_zamowienia = ?",
//                order.getStatus().toString(), order.getId());
//	}

    @Override
    public void updateOrderStatusAndProcess(Order order) {
        this.jdbcTemplate.update(
            "UPDATE logisticsystem.zamowienia " +
            "SET " +
            "czy_przetwarzane = ?, " +
            "status = ? " +
            "WHERE id_zamowienia = ?",
        order.isProcess(), order.getStatus().toString(), order.getId());
    }

    @Override
    public void removeOderDocuments(long orderId) {
        this.jdbcTemplate.update("DELETE FROM logisticsystem.dokumenty WHERE id_zamowienia = ?",
                new Object[]{orderId});
    }

    @Override
    public void setDocuments(final List<Document> documents, final long orderId) {
        String sql = "INSERT INTO logisticsystem.dokumenty " +
                "(nazwa, tresc, id_zamowienia) VALUES (?, ?, ?)";

        this.jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Document document = documents.get(i);
                ps.setString(1, document.getName());
                ps.setString(2, document.getText());
                ps.setLong(3, orderId);
            }

            @Override
            public int getBatchSize() {
                return documents.size();
            }
        });
        
    }

    @Override
    public List<Document> getDocuments(long orderId){
        List<Document> orderDocuments = this.jdbcTemplate.query(
            "SELECT d.id_dokumentu, d.nazwa, d.tresc " +
            "FROM logisticsystem.dokumenty d " +
            "INNER JOIN logisticsystem.zamowienia z ON ( d.id_zamowienia = z.id_zamowienia ) " +
            "WHERE z.id_zamowienia = ? ",

            new RowMapper<Document>() {
                public Document mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Document document = new Document();
                    document.setId(rs.getInt("id_dokumentu"));
                    document.setName(rs.getString("nazwa"));
                    document.setText(rs.getString("tresc"));
                    return document;
                }
            },
            new Object[]{orderId}
        );
        return orderDocuments;
    }

    @Override
    public List<Article> getOrderArticles(long orderId) {
        List<Article> orderArticles = this.jdbcTemplate.query(
            "SELECT t.id_towaru, t.nazwa, t.opis, tz.czy_zarezerwowane " +
            "FROM logisticsystem.towary_zamowien tz " +
            "INNER JOIN logisticsystem.towary t ON ( tz.id_towaru = t.id_towaru  ) WHERE tz.id_zamowienia = ?",

            new RowMapper<Article>() {
                public Article mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Article article = new Article();
                    article.setId(rs.getInt("id_towaru"));
                    article.setName(rs.getString("nazwa"));
                    article.setText(rs.getString("opis"));
                    article.setReserved(rs.getBoolean("czy_zarezerwowane"));
                    return article;
                }
            },
            new Object[]{orderId}
        );
        return orderArticles;
    }

    @Override
    public void setOrderArticles(final List<Article> orderArticles, final long orderId) {
        String sql = "UPDATE logisticsystem.towary_zamowien " +
                " SET czy_zarezerwowane = ? WHERE id_zamowienia = ? AND id_towaru = ?";

        this.jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                Article article = orderArticles.get(i);
                ps.setBoolean(1, article.isReserved());
                ps.setLong(2, orderId);
                ps.setInt(3, article.getId());
            }

            @Override
            public int getBatchSize() {
                return orderArticles.size();
            }
        });
    }

    @Override
    public boolean isOrderComplete(long orderId) {
        boolean result = this.jdbcTemplate.queryForObject(
            "SELECT bool_and(czy_zarezerwowane) " +
            "FROM   logisticsystem.towary_zamowien " +
            "WHERE  id_zamowienia = ?;",
            new Object[]{orderId},
            Boolean.class
        );

        return result;
    }

    public Order getOrderById(final Long orderId) {
        Order order = this.jdbcTemplate.queryForObject(
            "SELECT * " + //z.data_zlozenia, z.kod_ostatniej_akcji, z.komunikat_ostatniej_akcji, z.czy_przetwarzane, z.status " +
                    "FROM logisticsystem.zamowienia z WHERE z.id_zamowienia = ?;",
            new Object[]{ orderId },
            new RowMapper<Order>() {
                public Order mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Order order = new Order();
                    order.setId(orderId);
                    order.setLastActionComment(rs.getString("komunikat_ostatniej_akcji"));
                    order.setProcess(rs.getBoolean("czy_przetwarzane"));
                    order.setStatus(Order.OrderStatus.valueOf(rs.getString("status")));
                    return order;
                }
            });
        return order;
	}

	public List<String> getWarehouseNames() {
        List<String> warehouseNames = this.jdbcTemplate.query(
            "SELECT magazyny.nazwa as nazwa FROM logisticsystem.magazyny;",

            new RowMapper<String>() {
                public String mapRow(ResultSet rs, int rowNum) throws SQLException {
                    return rs.getString("nazwa");
                }
            });
        return warehouseNames;
	}

	@Override
	public OrderStatus getNextStatus(OrderStatus status) {
        OrderStatus orderStatus = this.jdbcTemplate.queryForObject(
		    "SELECT pb.nastepny_status  " +
            "FROM logisticsystem.proces_biznesowy pb WHERE pb.status = ?",
        new Object[]{ status.toString() },
            new RowMapper<OrderStatus>() {
                public OrderStatus mapRow(ResultSet rs, int rowNum) throws SQLException {
                    return Order.OrderStatus.valueOf(rs.getString("nastepny_status"));
                }
            });
		return orderStatus;
	}

	@Override
	public String getParam(String paramName) {
        String paramValue = this.jdbcTemplate.queryForObject(
            "SELECT wartosc " +
            "FROM logisticsystem.parametry  " +
            "WHERE nazwa = ? ;",
            new Object[]{paramName}, String.class
        );
        return paramValue;
	}

    @Override
    public boolean reserveArticle(String warehouseName, int articleId) {
        int result = this.jdbcTemplate.update(
            "UPDATE logisticsystem.towary_magazynow tm SET liczba_dostepnych = (liczba_dostepnych - 1) " +
                    "WHERE  tm.id_towaru = ? AND tm.id_magazynu in " +
                    "(SELECT id_magazynu FROM logisticsystem.magazyny WHERE nazwa = ? ) " +
                    "AND liczba_dostepnych > 1;",
            articleId, warehouseName);

        return result == 1 ? true : false;
    }

    @Override
    public void produceArticle(String warehouseName, int articleId) {
        this.jdbcTemplate.update(
            "UPDATE logisticsystem.towary_magazynow tm SET liczba_dostepnych = (liczba_dostepnych + 1) " +
            "WHERE  tm.id_towaru = ? AND tm.id_magazynu in " +
            "(SELECT id_magazynu FROM logisticsystem.magazyny WHERE nazwa = ? )",
                articleId, warehouseName);
    }

    @Override
    public int getNumberOfArticles(String warehouseName, int articleId){
        Integer numberOfArticles = this.jdbcTemplate.queryForObject(
            "SELECT tm.liczba_dostepnych " +
            "FROM logisticsystem.towary_magazynow tm " +
            "INNER JOIN logisticsystem.magazyny m ON ( tm.id_magazynu = m.id_magazynu  )  " +
                    "WHERE id_towaru = ? AND nazwa = ?;",
            new Object[]{articleId, warehouseName}, Integer.class
        );
        return numberOfArticles;
    }
}
